package database.dbObjects;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import objects.Course;

import main.Main;


public interface IDBCourse extends IDBObject {
	
	public class DBCourse extends DBObject {
		
		/*
		 * methods are final for optimization of the code (by the compiler)
		 * it is not possible anymore to inherit it after this declaration 
		 */
		
		public final static int maxLengthName = 50;
		
		
		public final void init() throws Exception {
			
			createTable();
			createTriggers();
		}
		
		private final void createTable() throws Exception {
			
			String query = "create table Course( " +
	        			   "Name varchar(" + maxLengthName + ") unique not null, " +
	        			   "Description varchar(" + maxLengthDesc + ") not null," +
	        			   "BeginYear integer not null, " +
	        			   "Semester integer not null, " +
	        			   "MoreInformation varchar(" + maxLengthInfo + "), " +
	        			   "primary key (Name) " +
	        			       "on conflict abort, " +
	        			   "check (Semester in (1,2,3)));";
						   // 1rst semester, 2nd semester or whole year
	        
			executeCreation(query);
		}
		
		private final void createTriggers() throws Exception {
			
			String query;
			query = "create trigger update_course_name after update of Name on Course " +
			   	    "begin " +
			   	    	"update Lesson set Course_Name = new.Name " +
			   	    		"where (Course_Name = old.Name); " +
				    	"update RelCourseFile set Course_Name = new.Name " +
				    		"where (Course_Name = old.Name); " +
				    	"update RelCourseTask set Course_Name = new.Name " +
				    		"where (Course_Name = old.Name); " +
				    	"update RelCourseTeacher set Course_Name = new.Name " +
				    		"where (Course_Name = old.Name); " +
				    "end;";
			executeCreation(query);
			
			query = "create trigger delete_course_name before delete on Course " +
					"begin " +
						"delete from Lesson where (Course_Name = old.Name); " +
	    	   			"delete from RelCourseFile where (Course_Name = old.Name); " +
	    	   			"delete from RelCourseTask where (Course_Name = old.Name); " +
	    	   			"delete from RelCourseTeacher where (Course_Name = old.Name); " +
	    	   		"end;";
			executeCreation(query);
		}
	
		public final boolean add(String name, String desc, Integer year,
				Integer semester, String moreInfo) throws Exception {
			
			checkConsistance(name, desc, year, semester, moreInfo);
			
			Vector<Object> v = new Vector<Object>(5);
			v.add(0, name);
			v.add(1, desc);
			v.add(2, year);
			v.add(3, semester);
			v.add(4, moreInfo);
			
			return super.add("Course", v);
		}
		
		public final boolean delete(String name) throws Exception {
			
			checkName(name);
			
			return super.delete("Course", "Name", name);
		}
		
		public final boolean updateName(String name, String newName) throws Exception {
			
			checkName(name);
			checkName(newName);
			
			return super.update("Course", "Name", name, "Name", newName);
		}
		
		public final boolean updateBeginYear(String name, Integer newYear) throws Exception {
			
			checkName(name);
			checkYear(newYear);
			
			return super.update("Course", "Name", name, "BeginYear", newYear);
		}
		
		public final boolean updateSemester(String name, Integer newSemester) throws Exception {
			
			checkName(name);
			checkSemester(newSemester);
			
			return super.update("Course", "Name", name, "Semester", newSemester);
		}
		
		public final boolean updateMoreInfo(String name, String newInfo) throws Exception {
			
			checkName(name);
			checkMoreInformation(newInfo);
			
			return super.update("Course", "Name", name, "MoreInformation", newInfo);
		}
				
		public Course getCourse(String name) throws Exception {
			// Returns the requested object or null if nothing was found
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Course " +
											 "where (Name = '" + name + "');");
			if (rs.next()) {
				Course c = new Course(rs.getString("Name"), 
						rs.getString("Description"), rs.getInt("BeginYear"), 
						rs.getInt("Semester"), rs.getString("MoreInformation"));
				rs.close();
				return c;
			}
			rs.close();
			return null;
		}
		
		public Vector<Course> getAllCourses() throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from Course;");
			
			Vector<Course> v = new Vector<Course>();
			int counter = 0;
			while (rs.next()) {
				v.add(counter, new Course(rs.getString("Name"), 
						rs.getString("Description"), rs.getInt("BeginYear"), 
						rs.getInt("Semester"), rs.getString("MoreInformation")));
				counter++;
			}
			rs.close();
			return v;
		}
		
		private final void checkConsistance(String name, String desc, Integer year, 
				Integer semester, String moreInfo) throws Exception {
			
			checkName(name);
			checkDescription(desc);
			checkYear(year);
			checkSemester(semester);
			checkMoreInformation(moreInfo);
		}
		
		public final static void checkName(String name) throws Exception {
			
			if (name == null) { throw (new Exception("Name needed!")); }
			if (name.length() < 1) {
				throw (new Exception("Name must be at least 1 character long!"));
			}
			if (name.length() > maxLengthName) {
				throw (new Exception("Name exceeds maximum length (" +
						maxLengthName + " characters)!"));
			}
		}
		
		private final void checkDescription(String desc) throws Exception {
			
			if (desc == null) { throw (new Exception("Description needed!")); }
			if (desc.length() < 1) {
				throw (new Exception("Description must be at least 1 character long!"));
			}
			if (desc.length() > maxLengthDesc) {
				throw (new Exception("Description exceeds maximum length (" +
						maxLengthDesc + " characters)!"));
			}
		}
		
		private final void checkYear(Integer year) throws Exception {
			
			if (year == null) { throw (new Exception("Year needed!")); }
			if (year < 0) { throw (new Exception("Year can't be negative!")); }
		}
		
		private final void checkSemester(Integer semester) throws Exception {
			
			if (semester == null) { throw (new Exception("Semester needed!")); }
			if (!((semester == 1) || (semester == 2) || (semester == 3))) { 
				throw (new Exception("Semester must be one of the following: \n" +
						"1 (first semester), 2 (second semester) or 3 (whole year)"));
			}
		}
		
		private final void checkMoreInformation(String moreInfo) throws Exception {
			
			if (moreInfo == null) { moreInfo = "Nothing."; }
			if (moreInfo.length() > maxLengthInfo) {
				throw (new Exception("MoreInformation exceeds maximum length (" +
						maxLengthInfo + " characters)!"));
			}
		}
		
		
		private final int maxLengthDesc = 500;
		private final int maxLengthInfo = 1000;
		
	}
	
}
